﻿Imports System.Data.SqlClient

Public Module SQLHelper


    ''' <summary>
    ''' 
    ''' </summary>
    ''' <param name="ConnOBJ"></param>
    ''' <param name="ds"></param>
    ''' <param name="strSQL"></param>
    ''' <returns>返回类型是 元组 Tuple,相当于包裹</returns>
    Public Function ExecuteSQLCMD(ConnOBJ As SqlConnection, ds As DataSet, strSQL As String) As (isSuccess As Boolean, ds As DataSet)

        Try
            'Trim是为了 减掉 首字符连续的空格，以及末字符连续的空格
            Dim sTokens = Split(strSQL.Trim())
            If InStr("INSERT,DELETE,UPDATE",
                   UCase$(sTokens(0))) Then
                Dim cmd = New SqlCommand(strSQL, ConnOBJ)
                cmd.CommandTimeout = 3
                Dim Reader = cmd.ExecuteNonQuery()
            Else
                Dim Adapter = New SqlDataAdapter(strSQL, ConnOBJ)
                Adapter.Fill(ds)
            End If

            Return (True, ds)
        Catch ex As Exception
            Return (False, ds)
        End Try

    End Function

    Public Function ExecuteSQLProdure(ConnOBJ As SqlConnection, ds As DataSet, strSQL As String, TableName As String) As (isSuccess As Boolean, ds As DataSet)

        Try
            'Trim是为了 减掉 首字符连续的空格，以及末字符连续的空格
            Dim sTokens = Split(strSQL.Trim())
            If InStr("EXEC",
                   UCase$(sTokens(0))) Then
                Dim cmd = New SqlCommand(strSQL, ConnOBJ)
                cmd.CommandType = CommandType.StoredProcedure
                Dim Adapter = New SqlDataAdapter(cmd)
                Adapter.Fill(ds, TableName)
            End If
            Return (True, ds)
        Catch ex As Exception
            Return (False, ds)
        End Try

    End Function

End Module
